package com.childenglish.mapper;

import com.childenglish.entity.SysStudentClass;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface SysStudentClassMapper {

    /**
     * 根据ID查询学生班级关联
     */
    @Select("SELECT * FROM sys_student_class WHERE id = #{id}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "classId", column = "class_id"),
            @Result(property = "joinTime", column = "join_time"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time")
    })
    SysStudentClass findById(@Param("id") Long id);

    /**
     * 根据学生ID查询班级关联列表
     */
    @Select("SELECT * FROM sys_student_class WHERE student_id = #{studentId} AND status = '0' ORDER BY join_time DESC")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "classId", column = "class_id"),
            @Result(property = "joinTime", column = "join_time"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time")
    })
    List<SysStudentClass> findByStudentId(@Param("studentId") Long studentId);

    /**
     * 根据班级ID查询学生列表
     */
    @Select("SELECT * FROM sys_student_class WHERE class_id = #{classId} AND status = '0' ORDER BY join_time")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "classId", column = "class_id"),
            @Result(property = "joinTime", column = "join_time"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "create_time")
    })
    List<SysStudentClass> findByClassId(@Param("classId") Long classId);

    /**
     * 查询学生是否已在某个班级
     */
    @Select("SELECT COUNT(*) FROM sys_student_class WHERE student_id = #{studentId} AND class_id = #{classId} AND status = '0'")
    int existsByStudentIdAndClassId(@Param("studentId") Long studentId, @Param("classId") Long classId);

    /**
     * 插入学生班级关联
     */
    @Insert("INSERT INTO sys_student_class (student_id, class_id, join_time, status, create_time) " +
            "VALUES (#{studentId}, #{classId}, #{joinTime}, #{status}, #{createTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(SysStudentClass sysStudentClass);

    /**
     * 更新学生班级关联状态
     */
    @Update("UPDATE sys_student_class SET status = #{status} WHERE id = #{id}")
    int updateStatus(@Param("id") Long id, @Param("status") String status);

    /**
     * 删除学生班级关联（软删除，更新状态为转出）
     */
    @Update("UPDATE sys_student_class SET status = '2' WHERE id = #{id}")
    int deleteById(@Param("id") Long id);

    /**
     * 统计班级学生数
     */
    @Select("SELECT COUNT(*) FROM sys_student_class WHERE class_id = #{classId} AND status = '0'")
    int countByClassId(@Param("classId") Long classId);

    /**
     * 统计学生所在班级数
     */
    @Select("SELECT COUNT(*) FROM sys_student_class WHERE student_id = #{studentId} AND status = '0'")
    int countByStudentId(@Param("studentId") Long studentId);
}

